/*
 * RecycleBinDAO.java
 *
 * Created on April 7, 2007, 8:44 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.group1E.data;

import edu.byu.isys413.group1E.handlers.Cache;
import edu.byu.isys413.group1E.handlers.ConnectionPool;
import edu.byu.isys413.group1E.handlers.ConnectionPoolException;
import edu.byu.isys413.group1E.handlers.DataException;
import edu.byu.isys413.group1E.handlers.GUID;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/** This class is created to provide interaction with the RecycleBin table in the database.
 *
 * @author tkulbeth
 */
public class RecycleBinDAO{
    
    /** Contains an instance of RecycleBinDAO*/
    private static RecycleBinDAO instance = null;
    
    /** Creates a new instance of RecycleBinDAO */
    public RecycleBinDAO() {
    }
    
    /**Retrieves an instance of the RecycleBinDAO object
     *
     * @return a current singleton instance of the RecycleBinDAO class
     */
    public static synchronized RecycleBinDAO getInstance() {
        if (instance == null) {
            instance = new RecycleBinDAO();
        }
        return instance;
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   CREATE
    /**
     * Creates a new RecycleBinBO object with a newly generated GUID.
     *
     * @return a newly created RecycleBinBO with unique GUID
     * @throws java.lang.Exception to catch any errors in the creation process.
     */
    public RecycleBinBO create() throws Exception{
        String id = GUID.generate();
        RecycleBinBO bo = new RecycleBinBO(id);
        Cache c = Cache.getInstance();
        c.put(bo.getId(), bo);
        return bo;
    }
    
    //////////////////////////////////////////////////////////////////////////////
    //////////   READ
    /**
     * This is the public read statement.  It loads an existing rbon record
     * from the database based on the rbon id and returns a RecycleBinBO.
     * 
     * @return a RecycleBinBO containing the information extracted from the requested
     * tuple in the DB.
     * @param id contains the unique identifier for the record to be read from the DB.
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized RecycleBinBO read(String id) throws DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        RecycleBinBO rb = (RecycleBinBO)c.get(id);
        if (rb != null){
            return rb;                                                        // if so, return it immediately
        }
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            rb = this.read(id, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for id = " + id, e);
        }catch (ConnectionPoolException x) {
            //rollback
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return rb;
    }
    
    
    /**
     * This is the public read statement.  It loads an existing rbon record
     * from the database based on the rbon id, using an already existing connection to the DB,
     * and returns a RecycleBinBO.
     *
     * @return RecycleBinBO
     * @param id contains the given id for the rbon to be read
     * @param conn contains an already open connection to the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */
    synchronized RecycleBinBO read(String id, Connection conn) throws SQLException, DataException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        RecycleBinBO rb = (RecycleBinBO)c.get(id);
        if (rb != null){
            return rb;                                                        // if so, return it immediately
        }
        
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM RecycleBin WHERE BackupID = '" + id + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            rb = new RecycleBinBO(rs.getString("BackupID"));
            rb.setExt(rs.getString("ext"));
            rb.setDateUploaded(rs.getDate("dateUploaded").toString());
            rb.setDateTrashed(rs.getDate("dateTrashed").toString());
            rb.setFileName(rs.getString("thisFileName"));
            rb.setFileSize(rs.getDouble("fileSize"));
            rb.setMembershipID(rs.getString("membershipID"));
            rb.setStatus(rs.getString("Status"));
            rb.setAlreadyInDB(true);
            rb.setIsDirty(false);
            
            // save to the cache
            c.put(rb.getId(), rb);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return rb;
    }
    
    /**
     * This is a public method to retrieve a list of all rbons in the DB.
     * 
     * @return a list of all rbons from the DB.
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized ArrayList<RecycleBinBO> readAllrb() throws DataException, SQLException, ConnectionPoolException{
        ArrayList<RecycleBinBO> rbList = new ArrayList<RecycleBinBO>();
        //now build the query
        String query = "SELECT * FROM RecycleBin";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
        //extract the elements from the result set and build a list of results
        //build the object from the result set
        //build the object from the result set
        while (rs.next()) {
            RecycleBinBO rb = new RecycleBinBO(rs.getString("BackupID"));
            rb.setExt(rs.getString("ext"));
            rb.setDateUploaded(rs.getDate("dateUploaded").toString());
            rb.setDateTrashed(rs.getDate("dateTrashed").toString());
            rb.setFileName(rs.getString("thisFileName"));
            rb.setFileSize(rs.getDouble("fileSize"));
            rb.setMembershipID(rs.getString("membershipID"));
            rb.setStatus(rs.getString("Status"));
            rb.setAlreadyInDB(true);
            rb.setIsDirty(false);
            
            // save to the list
            rbList.add(rb);
        }//while
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return rbList;
    }

    /**
     * This is a public method to retrieve a list of all backups in the recycle bin for a specified member in the DB.
     * 
     * @return a list of all bkDataons from the DB.
     * @param memberID contains the id of the member to retrieve the objects for
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized ArrayList<RecycleBinBO> readByMember(String memberID) throws DataException, SQLException, ConnectionPoolException{
        ArrayList<RecycleBinBO> rbDataList = new ArrayList<RecycleBinBO>();
        //now build the query
        String query = "SELECT * FROM RecycleBin WHERE MembershipID = '" + memberID + "'";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
        //extract the elements from the result set and build a list of results
        //build the object from the result set
        //build the object from the result set
        while (rs.next()) {
            RecycleBinBO rb = new RecycleBinBO(rs.getString("BackupID"));
            rb.setExt(rs.getString("ext"));
            rb.setDateUploaded(rs.getDate("dateUploaded").toString());
            rb.setDateTrashed(rs.getDate("dateTrashed").toString());
            rb.setFileName(rs.getString("thisFileName"));
            rb.setFileSize(rs.getDouble("fileSize"));
            rb.setMembershipID(rs.getString("membershipID"));
            rb.setStatus(rs.getString("Status"));
            rb.setAlreadyInDB(true);
            rb.setIsDirty(false);
            
            // save to the list
            rbDataList.add(rb);
        }//while
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return rbDataList;
    }      
    
    //////////////////////////////////
    ///   UPDATE
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) wants to save or update an object
     * into the database.
     * 
     * @param rbIn is a RecycleBinBO object to be saved
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized void save(RecycleBinBO rbIn) throws DataException, ConnectionPoolException {
        
        try {
            Connection conn = ConnectionPool.getInstance().get();
            try {
                this.save(rbIn, conn);                                   // call save with a connection
                conn.commit();
            } catch (ConnectionPoolException ex) {
                conn.rollback();
                ex.printStackTrace();
            } catch (DataException ex) {
                conn.rollback();
                ex.printStackTrace();
            } catch (SQLException ex) {
                conn.rollback();
                ex.printStackTrace();
                throw new SQLException();
            }
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            // rollback
            throw new DataException("Error saving record for Backup ID =" + rbIn.getId(), e);
        }catch (ConnectionPoolException x){
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return;
    }
    
    /**
     * This is called to save the RecycleBin object to the database using a previously created connection.
     * 
     * @param rb contains the RecycleBinBO to be saved
     * @param conn contains the Connection that has been established to the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized void save(RecycleBinBO rb, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // run update or insert
        if (rb.isIsDirty()) {
            if (rb.isAlreadyInDB()) {
                update(rb, conn);
            }else{
                insert(rb, conn);
            }
            // set the dirty flag to false now that we've saved it
            rb.setIsDirty(false);
        }
        
        
        // touch the cache for the object
        Cache c = Cache.getInstance();
        c.put(rb.getId(), rb);
    }
    
    /**
     * This method is called when a record needs to be updated in the DB.
     *
     * @param rb contains the RecycleBinBO to be updated to the DB
     * @param conn contains the connection already established with the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */
    private synchronized void update(RecycleBinBO rb, Connection conn) throws SQLException, DataException {
        //update the DB
        PreparedStatement pstmt = conn.prepareStatement("UPDATE RecycleBin SET ext = ?, thisFileName = ?, " +
                "fileSize = ?, dateUploaded = ?, dateTrashed = ?, Status = ?, MembershipID = ? WHERE BackupID = ?");
        pstmt.setString(1, rb.getExt());
        pstmt.setString(2, rb.getFileName());
        pstmt.setDouble(3, rb.getFileSize());
        pstmt.setDate(4, java.sql.Date.valueOf(rb.getDateUploaded()));
        pstmt.setDate(5, java.sql.Date.valueOf(rb.getDateTrashed()));
        pstmt.setString(6, rb.getStatus());
        pstmt.setString(7, rb.getMembershipID());
        pstmt.setString(8, rb.getId());
        pstmt.executeUpdate();
        pstmt.close();
        rb.setAlreadyInDB(true);
    }
    
    /**
     * This method is called by the save method to insert a new record into the database.
     * 
     * @param rb contains the RecycleBinBO to be updated to the DB
     * @param conn contains the connection already established with the DB
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */
    private synchronized void insert(RecycleBinBO rb, Connection conn) throws SQLException, DataException {
        //update the DB
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO RecycleBin (BackupID, ext, thisFileName, fileSize, " +
                "dateUploaded, dateTrashed, Status, MembershipID) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        pstmt.setString(1, rb.getId());
        pstmt.setString(2, rb.getExt());
        pstmt.setString(3, rb.getFileName());
        pstmt.setDouble(4, rb.getFileSize());
        pstmt.setDate(5, java.sql.Date.valueOf(rb.getDateUploaded()));
        pstmt.setDate(6, java.sql.Date.valueOf(rb.getDateTrashed()));
        pstmt.setString(7, rb.getStatus());
        pstmt.setString(8, rb.getMembershipID());
        pstmt.executeUpdate();
        pstmt.close();
        rb.setAlreadyInDB(true);
    }
    
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   DELETE
    
    /**
     * I included the delete functionality here because after a file has been in the recycle bin
     * for a certain amount of time, it will be removed permanently.
     * 
     * @param rbID contains the id of the item to be deleted
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    private synchronized void delete(String rbID) throws SQLException, DataException, ConnectionPoolException {
        //update the DB
        Connection conn = ConnectionPool.getInstance().get();
        PreparedStatement pstmt = conn.prepareStatement("DELETE FROM RecycleBin WHERE BackupID = ?");
        pstmt.setString(1, rbID);
        pstmt.executeUpdate();
        pstmt.close();
        ConnectionPool.getInstance().release(conn);
    }
}
